<?php
if (!defined("BASEPATH")) exit("No direct script access allowed");

class Scholarship extends MY_Model {

	function __construct() {
		parent::__construct();
		$this->tableName = "scholarship";
		$this->prefix = "sch";
	}

	function getScholarship($where = NULL, $start = NULL, $limit = NULL, $fields = NULL, $query = NULL, $order = "schName", $dir = "ASC") {

		$fieldsQuery = Common::fieldsquery($fields, $query);
		$order = Common::coalesce(array($order, "schName"));
		$dir = Common::coalesce(array($dir, "ASC"));

		$qs =	"
					select
						sch_id, sch_name, sch_sub_id, sch_url_name, sch_list_date, sch_presenter, t.e2c_temp_flag,
						description.e2c_text as description,
						sub_name,
						s2c_id,
						s2c.ins_id, s2c.ins_name, s2c.ins_url_name, city_name, city_url_name,
						
						cou_id, cou_name, cou_url_name, con_name, con_url_name,
						
						maj_id, maj_name, maj_url_name, majins.ins_name as maj_ins_name, majins.ins_url_name as  maj_ins_url_name
					from
						".$this->db->dbprefix.$this->tableName."
						join ".$this->db->dbprefix."sch2cou_ins_maj on sch_id = s2c_sch_id and s2c_del = 0
						left join ".$this->db->dbprefix."subject on sub_id = sch_sub_id and sub_del = 0
						
						left join ".$this->db->dbprefix."institute as s2c on s2c.ins_id = s2c_ins_id and s2c.ins_del = 0
						left join ".$this->db->dbprefix."city on city_id = ins_city_id and city_del = 0
						
						left join ".$this->db->dbprefix."country on cou_id = s2c_cou_id and cou_del = 0
						left join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0
						
						left join ".$this->db->dbprefix."major on maj_id = s2c_maj_id and maj_del = 0
						left join ".$this->db->dbprefix."institute as majins on majins.ins_id = maj_ins_id and majins.ins_del = 0

						".((in_array($this->uri->segment(1), array("admin", "admin_lte")) and Common::isAdmin())?
						(" join ".$this->db->dbprefix."usr2ent on u2e_del = 0 and u2e_usr_id = '".$this->session->userdata("usrId")."' and (u2e_ins_id = s2c.ins_id or u2e_ins_id = majins.ins_id) and u2e_page_cat = 'SCH' "):(""))."

						left join ".$this->db->dbprefix."ent2cat on e2c_entity = 'SCH' and e2c_ent_id = sch_id and e2c_del = 0
						left join (
							select
								e2c_ent_id, e2c_temp_flag
							from
								".$this->db->dbprefix."ent2cat 
							where
								e2c_entity = 'SCH' and e2c_del = 0 and e2c_temp_flag = 'I'
							group by
								e2c_ent_id
						) as t on t.e2c_ent_id = sch_id

						left join ".$this->db->dbprefix."ent2cat as description on sch_id = description.e2c_ent_id and description.e2c_entity = 'SCH' and description.e2c_del = 0 and description.e2c_cat_id = '".$this->db->escape_str($this->config->item("categorySCHDescriptionId"))."'
						
					where
						sch_del = 0 ".((!is_null($where))?(" and ".$where):(""))." ".(($fieldsQuery)?(" and (".$fieldsQuery.") "):(""))."
					order by
						".(($order == "rand()")?($order):(Common::underscore($order)." ".$dir))."
					".((is_numeric($start))?(" limit ".$start.",".$limit):(""))."
						";
		// left join ".$this->db->dbprefix."category as description_cat on description_cat.cat_id = description.e2c_cat_id and description_cat.cat_del = 0 and description_cat.cat_entity = 'SCH' and description_cat.cat_id = '".$this->db->escape_str($this->config->item("categorySCHDescriptionId"))."'
		$result = $this->db->query($qs);
		//Common::pr($this->db->last_query());

		$getScholarship = array();
		if($result->num_rows()) {
			foreach ($result->result() as $row) {
				$getScholarship[$row->sch_id]["schId"] = $row->sch_id;
				$getScholarship[$row->sch_id]["schName"] = $row->sch_name;
				$getScholarship[$row->sch_id]["schSubId"] = $row->sch_sub_id;
				$getScholarship[$row->sch_id]["schSubName"] = $row->sub_name;
				$getScholarship[$row->sch_id]["schUrlName"] = $row->sch_url_name;
				$getScholarship[$row->sch_id]["schListDate"] = $row->sch_list_date;
				$getScholarship[$row->sch_id]["e2cTempFlag"] = $row->e2c_temp_flag;
				$getScholarship[$row->sch_id]["e2cDescription"] = $row->description;
				$getScholarship[$row->sch_id]["schPresenter"] = $row->sch_presenter;

				if($row->ins_id) {
					if(!isset($getScholarship[$row->sch_id]["INS"][$row->ins_id])) {
						$getScholarship[$row->sch_id]["insList"] = ((isset($getScholarship[$row->sch_id]["insList"]) and !isset($getScholarship[$row->sch_id]["INS"][$row->ins_id]))?($getScholarship[$row->sch_id]["insList"].",".$row->ins_id):($row->ins_id));
					}
					$getScholarship[$row->sch_id]["INS"][$row->ins_id] = array(
						"s2cId" => $row->s2c_id,
						"insName" => $row->ins_name,
						"insUrlName" => $row->ins_url_name,
						"cityName" => $row->city_name,
						"cityUrlName" => $row->city_url_name,
					);
				}
				if($row->cou_id) {
					if(!isset($getScholarship[$row->sch_id]["COU"][$row->cou_id])) {
						$getScholarship[$row->sch_id]["couList"] = ((isset($getScholarship[$row->sch_id]["couList"]) and !isset($getScholarship[$row->sch_id]["COU"][$row->cou_id]))?($getScholarship[$row->sch_id]["couList"].",".$row->cou_id):($row->cou_id));
					}
					$getScholarship[$row->sch_id]["COU"][$row->cou_id] = array(
						"s2cId" => $row->s2c_id,
						"couName" => $row->cou_name,
						"couUrlName" => $row->cou_url_name,
						"conName" => $row->con_name,
						"conUrlName" => $row->con_url_name,
					);
				}
				if($row->maj_id) {
					if(!isset($getScholarship[$row->sch_id]["MAJ"][$row->maj_id])) {
						$getScholarship[$row->sch_id]["majList"] = ((isset($getScholarship[$row->sch_id]["majList"]) and !isset($getScholarship[$row->sch_id]["MAJ"][$row->maj_id]))?($getScholarship[$row->sch_id]["majList"].",".$row->maj_id):($row->maj_id));
					}
					$getScholarship[$row->sch_id]["MAJ"][$row->maj_id] = array(
						"s2cId" => $row->s2c_id,
						"majName" => $row->maj_name,
						"majUrlName" => $row->maj_url_name,
						"insName" => $row->maj_ins_name,
						"insUrlName" => $row->maj_ins_url_name,
					);
				}
			}
		}
		$tmp_getScholarship = array();
		foreach($getScholarship as $val) {
			$tmp_getScholarship[] = $val;
		}
		return $tmp_getScholarship;
	}

	function getScholarshipCount($where = NULL, $fields, $query) {

		$fieldsQuery = Common::fieldsquery($fields, $query);

		$qs =	"
					select
						count(*) as cnt
					from
						".$this->db->dbprefix.$this->tableName."
						join ".$this->db->dbprefix."sch2cou_ins_maj on sch_id = s2c_sch_id and s2c_del = 0
						
						left join ".$this->db->dbprefix."institute as s2c on s2c.ins_id = s2c_ins_id and s2c.ins_del = 0
						left join ".$this->db->dbprefix."city on city_id = ins_city_id and city_del = 0
						
						left join ".$this->db->dbprefix."country on cou_id = city_cou_id and cou_del = 0
						left join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0
						
						left join ".$this->db->dbprefix."major on maj_id = s2c_maj_id and maj_del = 0
						left join ".$this->db->dbprefix."institute as majins on majins.ins_id = maj_ins_id and majins.ins_del = 0

						".((in_array($this->uri->segment(1), array("admin", "admin_lte")) and Common::isAdmin())?
						(" join ".$this->db->dbprefix."usr2ent on u2e_del = 0 and u2e_usr_id = '".$this->session->userdata("usrId")."' and (u2e_ins_id = s2c.ins_id or u2e_ins_id = majins.ins_id) and u2e_page_cat = 'SCH'"):(""))."

						left join ".$this->db->dbprefix."ent2cat on e2c_entity = 'SCH' and e2c_ent_id = sch_id and e2c_del = 0
						left join (
							select
								e2c_ent_id, e2c_temp_flag
							from
								".$this->db->dbprefix."ent2cat 
							where
								e2c_entity = 'SCH' and e2c_del = 0 and e2c_temp_flag = 'I'
							group by
								e2c_ent_id
						) as t on t.e2c_ent_id = sch_id

						left join ".$this->db->dbprefix."ent2cat as description on sch_id = description.e2c_ent_id and description.e2c_entity = 'SCH' and description.e2c_del = 0
						left join ".$this->db->dbprefix."category as description_cat on description_cat.cat_id = description.e2c_cat_id and description_cat.cat_del = 0 and description_cat.cat_entity = 'SCH' and description_cat.cat_id = '".$this->db->escape_str($this->config->item("categorySCHDescriptionId"))."'
						
					where
						sch_del = 0 ".((!is_null($where))?(" and ".$where):(""))." ".(($fieldsQuery)?(" and (".$fieldsQuery.") "):(""))."
					";
		$result = $this->db->query($qs);

		if($result->num_rows()) {
			foreach ($result->result() as $row) {
				return $row->cnt;
			}
		}
	}

}